Introduction¶

Purpose¶

The purpose of this notebook is to make a portfolio project to showcase some basic data science skills.

To do this a tutorial from Dataquest will be used.

This notebook will be a rehash of the Dataquest tutorial project. Then, in another, separate, notebook, a different project with the same format will be made.

Topic¶

The SAT scores of high-school students from New York City will be analysed, along with several demographic metrics.

The SAT is a test used in the United States to assess students' readiness for higher education. It is required to be allowed entry to many universities, so students need to do well in it.

The range of SAT scores has changed several times. For the datasets used in this notebook, the maximum score es 2400. The average SAT score of high schools is often used to rank them.

There have been claims of race or gender bias in the SAT, so this analysis will try to look into these claims.

Basic datasets¶

NYC Open Data publishes data about New York City across different categories. In this case, the interest is in education data.

The basic datasets to be used are:

• 2012 SAT results: School-level results for New York City on the SAT
• 2014 - 2015 DOE High School Directory: Directory of NYC High Schools

Additional datasets¶

To enhance the data from the basic datasets, several others will be added:

• 2006 - 2011 NYS Math Test Results By Grade - School Level - All Students: Results on the New York State Mathematics Tests, Grades 3 - 8
• 2010-2011 Class Size - School-level detail: Average class sizes for each school, by grade and program type
• 2010 AP (College Board) School Level Results: School level College Board AP results for 2010. Some universities in the US grant credits based on AP test scores
• 2005-2010 Graduation Outcomes - School Level: Graduation Outcomes - Cohorts of 2001 through 2006 (Classes of 2005 through 2010)
• 2006 - 2012 School Demographics and Accountability Snapshot: Annual school accounts of NYC public school student populations served by grade, special programs, ethnicity, gender and Title I funded programs
• 2011 NYC School Survey: Every year, all parents, all teachers, and students in grades 6 - 12 take the NYC School Survey
• School Districts: GIS data: Boundaries of School Districts: Layout of school districts

Background information¶

Before beginning the analysis, it's useful to have some background information to provide context for the data:

• New York City is divided into five boroughs
• NYC is divided up into 32 geographic districts, with a few tens of schools each
• The datasets have schools that are not high schools, so data cleaning will be necessary
• Every public school in New York City is assigned a unique DBN “District Borough Number”
• By computing district-level metrics, the GIS (Geographic Information System) data can be used to plot maps and show geographical differences

Data ingestion¶

The first part of the process is ingesting the data, or reading it in from the different sources (tabular files in this case).

To do this, the following code will:

• Loop through the files
• Read each file into a Pandas DataFrame
• Put each DataFrame into a Python dictionary

Once the data has been read, the .head() method can be used on the data dictionary to print the first five rows of each DataFrame.

Some useful details can be noted from the previous output:

• Most of the datasets contain a DBN column, which can be used to uniquely identify each school
• There is information that could be used to make a map (Location 1 column)
• In some datasets there are multiple rows for each school, so preprocessing will be necessary

Combining the datasets¶

To make working with the data easier, all the individual datasets will be combined into a single one. To do this, a column that exists in every dataset must be found. From what it was noted above, DBN might be that column.

Two of the datasets, hs_directory and class_size, don't contain a DBN column.

hs_directory dataset¶

In this case, the column exists, but it is called dbn, so it is only necessary to change its name to DBN.

class_size dataset¶

In the rest of the datasets, the DBN column looks like the following output.

But, looking at the five first class_size rows, no DBN column can be seen.

From the previous output, it can be observed that the DBN is formed with the CSD, BOROUGH, and SCHOOL CODE columns combined.

Looking at the dictionary of the dataset, the following description can be obtained:

• DBN stands for District, Borough, School Number, and every school in the system can be identified by this 6-digit code

• The first two numbers represent the school district (CSD, Community School District)

• The third character signifies the borough (BOROUGH) in which the school is located (M = Manhattan, X = Bronx, R = Staten Island, K = Brooklyn and Q = Queens)

• The final three digits represent the SCHOOL CODE and are unique within the borough

So the DBN column can be constructed for the class_size dataset.

School surveys¶

The NYC School Survey is taken every year by parents, teachers and students. It is meant to provide insight into the learning environment of schools, giving access to metrics that go beyond simple test scores.

The questions target the community opinions on academic expectations, communication, engagement, and safety and respect.

In 2011, 960,191 surveys were submitted.

The following code will:

• Read the surveys for all schools and the surveys for district 75
• Add a flag that indicates from which school district is each dataset
• Combine the datasets

Looking at the columns¶

The survey DataFrame has 2773 columns. To be able to easily compare columns and calculate correlations, this number should be reduced.

The survey data came with a data dictionary (in spreadsheet format) that explains the meaning of each column.

Not all columns will provide useful information for the analysis, so only the important ones will be kept:

• DBN: District, Borough, School Number
• rr_s: Student Response Rate
• rr_t: Teacher Response Rate
• rr_p: Parent Response Rate
• N_s: Number of student respondents
• N_t: Number of teacher respondents
• N_p: Number of parent respondents
• saf_p_11: Safety and Respect score based on parent responses
• com_p_11: Communication score based on parent responses
• eng_p_11: Engagement score based on parent responses
• aca_p_11: Academic expectations score based on parent responses
• saf_t_11: Safety and Respect score based on teacher responses
• com_t_11: Communication score based on teacher responses
• eng_t_11: Engagement score based on teacher responses
• aca_t_11: Academic expectations score based on teacher responses
• saf_s_11: Safety and Respect score based on student responses
• com_s_11: Communication score based on student responses
• eng_s_11: Engagement score based on student responses
• aca_s_11: Academic expectations score based on student responses
• saf_tot_11: Safety and Respect total score
• com_tot_11: Communication total score
• eng_tot_11: Engagement total score
• aca_tot_11: Academic Expectations total score

Understanding what each dataset contains, and which columns in each dataset are the relevant ones is crucial to avoid wasting time and effort later on, during the analysis phase.

Condensing datasets¶

If we again look at the first rows of each dataset, we'll see that in many cases there are several rows for each high school. But in the sat_results dataset, there is only one row per high school.

To be able to compare SAT scores with different variables, there should be only one row per high school.

Condensing class_size¶

In this dataset, the GRADE and PROGRAM TYPE columns have multiple values for each high school. By restricting each field to a single value, most of the duplicate rows can be filtered out.

The following code will:

• Only select values from class_size where the GRADE column equals 09-12 (high school grade 9th through 12th)
• Only select values from class_size where the PROGRAM TYPE column equals GEN ED (restrict values to general education programs only)
• Group class_size by DBN, then find the average class_size values for each school

Condensing demographics¶

The dataset contains data for several years, specified by the schoolyear column. Only the rows for the most recent year will be kept (20112012).

Condensing math_test_results¶

This dataset is segmented by Grade and Year. Only the data for the most recent year and highest grade will be kept.

Condensing graduation¶

In this case, the data used will be the one for the class of 2010 (Cohort=2006) and with metrics calculated across de entire cohort (Demographic=Total Cohort).

Resulting data¶

Data cleaning is an import step to avoid arriving at nonsense conclusions (to avoid the outcome of the old adage: garbage in, garbage out).

The resulting data is presented by the following code.

Computing variables¶

SAT score¶

The sat_results dataset has the average score by each of the three sections of the SAT exam in the SAT Critical Reading Avg. Score, SAT Math Avg. Score and SAT Writing Avg. Score columns. The total SAT score is simply the sum of these three columns.

Geographic coordinates¶

The GIS data from each school can be used to display them in a map. This information is contained in the Location 1 column from the hs_directory dataset.

The typical value of the Location 1 column is formed by three lines:

284 Baltic Street  
Brooklyn, NY 11201  
(40.685451806, -73.993491465)

The third row contains the latitude and longitude coordinates for the school. These values will be obtained using string splitting.

The result of computing the new variables is shown below.

Combining the datasets¶

In this stage the different datasets will be combined, using the DBN column.

Not all datasets have the same amount of rows (there exist high schools in some datasets that aren't present in the rest). When joining, it's important to not lose any data.

Given that the interest is in SAT scores, any high schools that don't exist in the sat_results will be ignored. To do this, the joins will all be left joins, with the left table being sat_results.

The below code will:

• Loop through each dataset
• Perform a left join

Deal with duplicates¶

It's important to deal with duplicate values so that they don't distort the metrics to be calculated.

In this case, there is only one duplicate row (possibly a typo), so removing the second instance of it (as it contains NA values) solves the problem.